"""
########################################
文件描述：数据库连接，查询，增删改操作的公共方法
########################################
"""
import pymssql
import yaml
from common.base_path import base_path
import os

class CommonDB:

    # 初始化数据库链接
    def __init__(self, config_path):
        # with open('../config/config.yaml', 'r', encoding='utf-8') as file:
        #     data = yaml.load(file)  # 读取yaml配置
        with open(config_path, 'r', encoding='utf-8') as file:
            data = yaml.safe_load(file)  # 读取yaml配置
        self.conn = pymssql.connect(host=data['host'],
                                    user=data['user'],
                                    password=data['passwd'],
                                    database=data['database'])
        self.cur = self.conn.cursor()

    # 析构函数，实例删除时触发
    def __del__(self):
        self.cur.close()
        self.conn.close()

    # 封装查询数据库操作
    def db_query(self, sql):

        self.cur.execute(sql)

        return self.cur.fetchall()

    # 封装更改数据库操作
    def db_change(self, sql):
        try:
            self.cur.execute(sql)  # 执行sql
            self.conn.commit()  # 提交更改
        except Exception as e:
            self.conn.rollback()  # 回滚
            print(str(e))


if __name__ == '__main__':
    project_path = base_path()
    dbconfig_path = os.path.join(project_path, 'config/dbconfig_SHTEST.yaml')
    print(dbconfig_path)
    l = CommonDB(dbconfig_path)
    # res = l.db_query("select CAST(NowMileage AS INT) from Auto where AutoNo like '%572%'")
    a = '苏D-W572T'
    res = l.db_query("select AutoNo, CAST(NowMileage AS INT) from Auto where AutoNo='%s'" % a)
    # newList = []
    # for a in range(len(res)):
    #     aa = str(res[a]).replace(',', '')
    #     b = aa.replace('(', '')
    #     c = b.replace(')', '')
    #     d = c.replace('\'', '')
    #     newList.append(d)
    print(res)
    print(res[0][1])

    # print(newList)


